# 数据库查询
import pymysql


# 查询
def query(sql):
    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='finance')
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL语句
    cursor.execute(sql)
    # 获取结果
    result = cursor.fetchall()
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
    return result


# 删除
def delete(sql):
    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='finance')
    # 创建游标
    cursor = conn.cursor()
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交事务
        conn.commit()
        return True
    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f"删除数据失败: {e}")
        return False
    finally:
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()


if __name__ == '__main__':
    # 查询user表
    result = query("SELECT * FROM user;")

    # 注意：这里应使用 conn 和 cursor 来正确获取 description
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='finance')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM user;")
    columns = [desc[0] for desc in cursor.description]
    cursor.close()
    conn.close()

    # 打印表头
    print(" | ".join(columns))

    # 打印每条记录
    for row in result:
        print(" | ".join(str(cell) for cell in row))
